package cn.edu.klmy.dao.impl;

import cn.edu.klmy.dao.AbstractJdbcTemplate;
import cn.edu.klmy.dao.IAuthorityDao;
import cn.edu.klmy.models.role.Role;
import cn.edu.klmy.models.role.RoleMember;
import cn.edu.klmy.models.teacher.Teacher;
import cn.edu.klmy.models.user.User;
import com.googlecode.ehcache.annotations.Cacheable;
import com.googlecode.ehcache.annotations.TriggersRemove;
import com.googlecode.ehcache.annotations.When;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 权限DAO实现类
 *
 * @author 顾伟
 *         创建日期：13-4-6
 */
@Repository
public class AuthorityDao extends AbstractJdbcTemplate implements IAuthorityDao {
    /**
     * 通过用户标识获取该用户所属的用户组ID
     *
     * @param userId 用户标识
     * @return 用户所属的用户组ID
     */
    @Override
    //@Cacheable(cacheName = "roleCache")
    public List<Integer> getRoleIdsByUserID(int userId) {
        //用户角色信息
        String ROLES_QUERY = "SELECT B.ID FROM ROLE B LEFT JOIN USER_ROLE A ON B.ID=A.ROLE_ID WHERE A.USER_ID=?";
        List<Integer> roleIds = this.jdbcTemplate.queryForList(ROLES_QUERY, Integer.class, new Object[]{userId});
        return roleIds;
    }

    //@Cacheable(cacheName = "roleCache")
    public List<Role> getAllRoles() {
        String ROLES_QUERY = "SELECT ID,ROLE_NAME,ROLE_CODE,ROLE_DESCRIPTION FROM ROLE";
        List<Role> roles = this.jdbcTemplate.query(ROLES_QUERY, new RoleRowMapper());
        return roles;
    }

    //@Cacheable(cacheName = "roleCache")
    public List<RoleMember> getRoleMember(int roleId) {
        String MEMBER_QUERY = "SELECT B.ID,C.TEACHER_NAME,C.TEACHER_SEX,C.TEACHER_RACE,C.TEACHER_ID_NO,C.TEACHER_BIRTH " +
                "FROM USER_ROLE A LEFT JOIN USER B ON A.USER_ID=B.ID " +
                "LEFT JOIN TEACHER C ON B.USER_ID_NO=C.TEACHER_ID_NO " +
                "WHERE A.ROLE_ID=? AND B.USER_TYPE<>0";
        List<RoleMember> member = this.jdbcTemplate.query(MEMBER_QUERY, new Object[]{roleId}, new RoleMemberRowMapper());
        return member;
    }

    //@Cacheable(cacheName = "roleCache")
    public List<Teacher> getTeachersNotInRole(int roleId){
        String sql="SELECT E.ID,D.TEACHER_NAME,D.TEACHER_BIRTH,D.TEACHER_SEX,D.TEACHER_RACE," +
                "D.TEACHER_SUBJECT,D.TEACHER_GRADE,D.TEACHER_ID_NO FROM TEACHER D " +
                "LEFT JOIN USER E ON D.TEACHER_ID_NO=E.USER_ID_NO " +
                "WHERE D.ID NOT IN (" +
                "SELECT C.ID FROM USER_ROLE A INNER JOIN USER B ON A.USER_ID=B.ID " +
                "INNER JOIN TEACHER C ON B.USER_ID_NO=C.TEACHER_ID_NO " +
                "WHERE A.ROLE_ID="+roleId+")";
        return jdbcTemplate.query(sql,new TeacherRowMapper());
    }

    //@TriggersRemove(cacheName = "roleCache", when = When.AFTER_METHOD_INVOCATION, removeAll = true)
    public RoleMember addMemberToRole(int memberId,int roleId){
        SimpleJdbcInsert insert = new SimpleJdbcInsert(this.jdbcTemplate);
        insert.withTableName("USER_ROLE");
        insert.compile();

        Map<String, Object> args = new HashMap<String, Object>();
        args.put("USER_ID",memberId);
        args.put("ROLE_ID",roleId);
        int count=insert.execute(args);

        RoleMember roleMember=null;
        if(count!=0){
            String MEMBER_QUERY = "SELECT B.ID,C.TEACHER_NAME,C.TEACHER_SEX,C.TEACHER_RACE,C.TEACHER_ID_NO,C.TEACHER_BIRTH " +
                    "FROM USER_ROLE A LEFT JOIN USER B ON A.USER_ID=B.ID " +
                    "LEFT JOIN TEACHER C ON B.USER_ID_NO=C.TEACHER_ID_NO " +
                    "WHERE A.ROLE_ID="+roleId+" AND B.ID="+memberId;
            roleMember=(RoleMember)this.jdbcTemplate.queryForObject(MEMBER_QUERY,new RoleMemberRowMapper());
        }
        return roleMember;
    }

    //@TriggersRemove(cacheName = "roleCache", when = When.AFTER_METHOD_INVOCATION, removeAll = true)
    public int deleteMemberFromRole(int memberId,int roleId){
        String sql="DELETE FROM USER_ROLE WHERE ROLE_ID="+roleId+" AND USER_ID="+memberId;
        int count=this.jdbcTemplate.update(sql);
        return count;
    }

    private static class TeacherRowMapper implements RowMapper {
        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            Teacher teacher = new Teacher();
            teacher.setId(resultSet.getInt("ID"));
            teacher.setBirthday(resultSet.getString("TEACHER_BIRTH"));
            teacher.setName(resultSet.getString("TEACHER_NAME"));
            teacher.setNation(resultSet.getInt("TEACHER_RACE"));
            teacher.setSex(resultSet.getInt("TEACHER_SEX"));
            teacher.setIdentityCardNumber(resultSet.getString("TEACHER_ID_NO"));
            teacher.setSubject(resultSet.getString("TEACHER_SUBJECT"));
            teacher.setGrade(resultSet.getString("TEACHER_GRADE"));
            return teacher;
        }
    }

    private class RoleMemberRowMapper implements RowMapper {
        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            RoleMember roleMember = new RoleMember();
            roleMember.setMemberId(resultSet.getInt("ID"));
            roleMember.setBirthday(resultSet.getString("TEACHER_BIRTH"));
            roleMember.setIdentityCardNumber(resultSet.getString("TEACHER_ID_NO"));
            roleMember.setMemberName(resultSet.getString("TEACHER_NAME"));
            roleMember.setNation(resultSet.getInt("TEACHER_RACE"));
            roleMember.setSex(resultSet.getInt("TEACHER_SEX"));
            return roleMember;
        }
    }

    /**
     * 角色映射
     */
    private class RoleRowMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            Role role = new Role();
            role.setRoleId(resultSet.getInt("ID"));
            role.setRoleName(resultSet.getString("ROLE_NAME"));
            role.setRoleCode(resultSet.getString("ROLE_CODE"));
            role.setRoleDescription(resultSet.getString("ROLE_DESCRIPTION"));
            return role;
        }
    }
    //=================陈瑞军添加
    private static final String ADD_PERMISSION = "INSERT INTO USER_ROLE(USER_ID, ROLE_ID) VALUES(?, ?)";
	@Override
	public void addDefaultPermission(int userId) {
		jdbcTemplate.update(ADD_PERMISSION, new Object[] { userId, 2 });
	}

    private static final String REMOVE_USER_PERMISSIONS = "DELETE FROM USER_ROLE WHERE USER_ID = ?";
	@Override
	public void removeUserPermissions(int userId) {
		jdbcTemplate.update(REMOVE_USER_PERMISSIONS, new Object[] { userId });
	}
}
